﻿CREATE TRIGGER [dbo].[vtri_Upload#Process]
ON [dbo].[v_Upload#Process]
INSTEAD OF INSERT
 AS 
DECLARE	@UploadID int,@PurchaseBatchID int,@InterestTypeID int, @PaymentTypeID int


SELECT 	@UploadID = UploadID,@PurchaseBatchID = PurchaseBatchID,
@InterestTypeID = InterestTypeID, @PaymentTypeID = PaymentTypeID
FROM inserted


DECLARE @ContractHolderID int,@CompanyID int
SELECT @ContractHolderID = ContractHolderID,@CompanyID = CompanyID
FROM t_UploadInfo UI INNER JOIN t_Upload U ON U.UploadInfoID = UI.ID WHERE U.ID = @UploadID

IF NOT EXISTS(SELECT * FROM t_PurchaseBatch WHERE ID = @PurchaseBatchID AND ContractHolderID = @ContractHolderID)BEGIN
	RAISERROR('Contract Holder must be the same in Upload and Purchase batches.',16,1)
	RETURN
END
IF NOT EXISTS(SELECT * FROM t_PurchaseBatch WHERE ID = @PurchaseBatchID AND CompanyID = @CompanyID)BEGIN
	RAISERROR('Company must be the same in Upload and Purchase batches.',16,1)
	RETURN
END


DECLARE @Context int EXEC @Context = sp__Context_ArchiveOff 1

DECLARE @LoanID int,@StatusID int SET @StatusID = dbo.fs_StateID__Batches__NewInBatch()
EXEC @LoanID = sec_AddNewLoan @CompanyID,@StatusID

UPDATE t_Loan SET ContractHolderID = (
SELECT ContractHolderID FROM t_UploadInfo UI INNER JOIN t_Upload U ON U.UploadInfoID = UI.ID WHERE U.ID = @UploadID
)
WHERE ID = @LoanID

INSERT INTO t_PurchaseBatch_Loans(BatchID,LoanID)VALUES(@PurchaseBatchID,@LoanID)
IF @@ERROR <> 0 RETURN

SELECT 'Main' T,    @LoanID AS LoanID,L_B.BorrowerID,
B.FirstName,
B.LastName,
B.SSN,
dbo.Trim(ISNULL(U.[Last Name], dbo.clr_RegEx_MatchGroup(U.[Full Name], N'^\s*([^\s,]+)', 1))) AS [Last Name],
dbo.Trim(ISNULL(U.[First Name], dbo.clr_RegEx_MatchGroup(U.[Full Name], N'^\s*[^\s,]+[\s,]*(.+?)\s*$', 1))) AS [First Name],
U.[Full Name], U.[Primary SSN], U.Address, U.City, U.St, U.[Zip Code], 
U.[Home Phone #], U.[Work Phone #], U.[Joint Name], U.[Joint Address], U.[Joint City], U.[Joint State], U.[Joint Zipcode], U.[Joint SSN], 
U.[Joint H Phone#], U.[Joint W Phone#], U.[Primary Cell #],
U.InterestTypeID,LPT.ID PaymentTypeID
INTO #Info
FROM         v_Upload AS U
INNER JOIN t_Loan_Borrower AS L_B ON L_B.LoanID = @LoanID
INNER JOIN t_Borrower AS B ON B.ID = L_B.BorrowerID
CROSS JOIN inserted i
LEFT OUTER JOIN ts_LoanPaymentType LPT ON LPT.PaymentsPerYear = U.[Pmt Freq]
WHERE     (U.ID = @UploadID)

IF EXISTS(SELECT * FROM #Info WHERE ISNULL(InterestTypeID,0) = 0 OR ISNULL(PaymentTypeID,0) = 0)BEGIN
	RAISERROR('Interest Type and Payment Type must not be empty.',16,1)
	ROLLBACK TRAN RETURN
END

UPDATE LI SET InterestType = I.InterestTypeID,PaymentTypeID = I.PaymentTypeID
FROM t_LoanInfo LI INNER JOIN #Info I ON I.LoanID = LI.LoanID

UPDATE B SET SSN = i.[Primary SSN],FirstName = i.[First Name],LastName = i.[Last Name],PhoneHome = i.[Home Phone #]
FROM t_Borrower B INNER JOIN #Info i ON i.BorrowerID = B.ID

UPDATE A SET Street = i.Address,CityName = i.City,StateCode = i.St,Zip = i.[Zip Code]
FROM t_Address A INNER JOIN t_Borrower B ON A.BorrowerID = B.ID INNER JOIN #Info i ON i.BorrowerID = B.ID WHERE A.Pos = 0

UPDATE E SET Phone = i.[Work Phone #]
FROM t_Employment E INNER JOIN t_Borrower B ON E.BorrowerID = B.ID INNER JOIN #Info i ON i.BorrowerID = B.ID WHERE E.Pos = 0

--SELECT '#Info' T,* FROM #Info
--SELECT 'Brwr' T,     B.ID, B.FirstName, B.LastName, B.SSN, B.PhoneHome, B.PhoneCell, B.FullName,
--A.Street, A.CityName, A.Zip, A.StateCode, E.Phone
--FROM         t_Borrower AS B INNER JOIN
--                      t_Address AS A ON A.BorrowerID = B.ID INNER JOIN
--                      t_Loan_Borrower AS L_B ON B.ID = L_B.BorrowerID INNER JOIN
--                      t_Employment E ON B.ID = E.BorrowerID
--WHERE     (L_B.LoanID = @LoanID) AND E.Pos = 0 AND A.Pos = 0

SELECT     @LoanID AS LoanID, U.[Car Year], U.Make, U.Model, U.Serial#, U.Mileage,
ISNULL(U.BookValue,U.[Black Book Value (Avg)])BookValue,
CASE WHEN BookValue IS NULL THEN 7 ELSE U.BookValueTypeID END BookValueTypeID
INTO [#CarInfo]FROM t_Upload AS U INNER JOIN t_Loan_Borrower AS L_B ON L_B.LoanID = @LoanID
WHERE     (U.ID = @UploadID)

SELECT * FROM #CarInfo
IF NOT EXISTS(SELECT * FROM t_Car WHERE VIN = (SELECT Serial# FROM #CarInfo))
	INSERT INTO t_Car(VIN)SELECT Serial# FROM #CarInfo

UPDATE C SET Year = i.[Car Year]
FROM t_Car C INNER JOIN #CarInfo i ON i.Serial# = C.VIN

UPDATE CI SET VIN = i.Serial#
FROM t_CarInfo CI INNER JOIN #CarInfo i ON i.LoanID = CI.LoanID

DELETE  CB FROM t_CarInfo_BookValues CB INNER JOIN #CarInfo CI ON CB.VIN = CI.Serial# AND CI.BookValueTypeID = CB.CarBookProvider_ValueTypeID

INSERT INTO t_CarInfo_BookValues(VIN,Price,MileageAddOn,CarBookProvider_ValueTypeID,Type)
SELECT Serial#,ISNULL(BookValue,0),0,BookValueTypeID,T.TypeName FROM #CarInfo CI
INNER JOIN ts_CarBookProvider_ValueType T ON T.ID = CI.BookValueTypeID

UPDATE [dbo].[v_BuyersOrder#Update] SET SalesPrice = (SELECT [Amt Financed] FROM t_Upload WHERE ID = @UploadID)WHERE LoanID = @LoanID
UPDATE [dbo].[v_BuyersOrder#Update] SET SalesTaxAmount = 0 WHERE LoanID = @LoanID

UPDATE LI SET
APROverride = U.APR,
PrincipalBalance = U.[Principal Balance],
ContractDate = U.[Open Date],
FirstPaymentDate = U.[1st# Payment Date],
PaymentOverride = U.[Payment Amount],
InterestType = U.InterestTypeID,
PaymentsPerYear = U.[Pmt Freq],
NumberOfPayments = U.[# of Payments]
FROM t_LoanInfo LI CROSS JOIN inserted i
INNER JOIN t_Upload U ON U.ID = i.UploadID
WHERE LI.LoanID = @LoanID

UPDATE t_Upload SET LoanID = @LoanID WHERE ID = @UploadID

SELECT @LoanID LoanID


